﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetBasicReference_BasicReferences')
	BEGIN
		DROP  Procedure  esf_sso.P_GetBasicReference_BasicReferences
	END
GO

CREATE Procedure esf_sso.P_GetBasicReference_BasicReferences
	@pBasicReferenceID			BIGINT
	,@pChildrenBasicReferenceTypeID		INT = NULL
AS
	
	SELECT 
		DISTINCT
		*
	FROM
		esf_sso.VBasicReference VBR
	INNER JOIN
		esf_sso.TreeBasicReference TBR ON BasicReferenceID = VBR.ID
	WHERE
		TBR.ParentBasicReferenceID = @pBasicReferenceID
		AND (@pChildrenBasicReferenceTypeID IS NULL OR VBR.BasicReferenceTypeID = @pChildrenBasicReferenceTypeID)
	ORDER BY
		VBR.Name
GO